﻿// WebZHX.Infrastructure/Repositories/Base/DapperRepository.cs
using Common.Models;
using Core.Interfaces;
using Dapper;
using Infrastructure.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Infrastructure.Repositories.Base
{
    /// <summary>
    /// Dapper 仓储
    /// </summary>
    public class DapperRepository : IDapperRepository
    {
        private readonly ApplicationDbContext _context;
        private readonly IConfiguration _configuration;

        public DapperRepository(ApplicationDbContext context, IConfiguration configuration)
        {
            _context = context;
            _configuration = configuration;
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns>数据库连接</returns>
        private IDbConnection CreateConnection()
        {
            return new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
        }

        /// <summary>
        /// 执行查询，返回多个实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>实体列表</returns>
        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object parameters = null)
        {
            using var connection = CreateConnection();
            return await connection.QueryAsync<T>(sql, parameters);
        }

        /// <summary>
        /// 执行查询，返回单个实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>实体</returns>
        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object parameters = null)
        {
            using var connection = CreateConnection();
            return await connection.QueryFirstOrDefaultAsync<T>(sql, parameters);
        }

        /// <summary>
        /// 执行命令（增、删、改）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响的行数</returns>
        public async Task<int> ExecuteAsync(string sql, object parameters = null)
        {
            using var connection = CreateConnection();
            return await connection.ExecuteAsync(sql, parameters);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL语句（不包含ORDER BY和分页）</param>
        /// <param name="parameters">参数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="orderBy">排序字段</param>
        /// <returns>分页结果</returns>
        public async Task<PagedResult<T>> QueryPagedAsync<T>(string sql, object parameters, int pageIndex, int pageSize, string orderBy = "Id")
        {
            using var connection = CreateConnection();

            // 获取总数
            var countSql = $"SELECT COUNT(1) FROM ({sql}) AS CountQuery";
            var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

            // 分页查询
            var pagedSql = $@"
                {sql}
                ORDER BY {orderBy}
                OFFSET {(pageIndex - 1) * pageSize} ROWS
                FETCH NEXT {pageSize} ROWS ONLY";

            var data = (await connection.QueryAsync<T>(pagedSql, parameters)).ToList();
  
            return new PagedResult<T>
            {
                Data = data,
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount
            };
        }
    }
}